﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using MockPrjTeam2.Common;
using MockPrjTeam2.Entity;

namespace MockPrjTeam2.DAO
{
    public class ServiceDao
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="ServiceDao" /> class.
        /// </summary>
        public ServiceDao()
        {
        }

        /// <summary>
        /// To the service list.
        /// </summary>
        /// <param name="dataTable">The data table.</param>
        /// <returns></returns>
        public static List<Service> ToServiceList(DataTable dataTable)
        {
            var reply = new List<Service>();
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    var service = Service.MappingEntity(row);
                    if (service != null)
                    {
                        reply.Add(service);
                    }
                }
            }

            return reply;
        }



        /// <summary>
        /// Gets all.
        /// </summary>
        /// <returns></returns>
        public List<Service> GetAll()
        {
            var reply = new List<Service>();
            const string query = 
            @"Select *,Contact.FirstName +' '+ Contact.Surname as ContactName 
            From Service inner join Contact on Service.ContactID = Contact.ContactID 
            where Service.IsActive=1 ORDER BY ServiceName ASC";
            using (var sqlHelper = new SqlHelper())
            {
                var dtService = sqlHelper.ExecuteSelectQuery(query);
                reply = ToServiceList(dtService);
            }

            return reply;
        }

        public int IsServiceNameExist(string serviceName)
        {
            const string query =
            @"Select Count(Service.ServiceName) From Service
            where Service.ServiceName Like '%'+@ServiceName+'%'";
            var sqlParameter = new SqlParameter[1];
            sqlParameter[0] = new SqlParameter { ParameterName = "@ServiceName", Value = serviceName };
            using (var sqlHelper = new SqlHelper())
            {
                return sqlHelper.ExecuteScalar(query, sqlParameter);
            }
        }

        /// <summary>
        /// Determines whether [is service name exist] [the specified service id].
        /// </summary>
        /// <param name="serviceId">The service id.</param>
        /// <returns></returns>
        public List<Service> IsServiceNameExist(int serviceId)
        {
            var reply = new List<Service>();
            const string query = @"select *, Contact.FirstName +' '+ Contact.Surname as ContactName 
                                from Service inner join Contact on Service.ContactID=Contact.ContactID
                                where Service.ServiceID != @ServiceID";
            var sqlParameter = new SqlParameter[1];
            sqlParameter[0] = new SqlParameter { ParameterName = "@ServiceID", Value = serviceId};
            using (var sqlHelper = new SqlHelper())
            {
                var dtService = sqlHelper.ExecuteSelectQuery(query,sqlParameter);
                reply = ToServiceList(dtService);
            }

            return reply;
        }

        /// <summary>
        /// Gets the by id.
        /// </summary>
        /// <param name="serviceId"> </param>
        /// <returns></returns>
        public List<Service> GetById(int serviceId)
        {
            var reply = new List<Service>();
            const string query = 
            @"select *, Contact.FirstName +' '+ Contact.Surname as ContactName 
            from Service inner join Contact on Service.ContactID=Contact.ContactID where ServiceID=@Id";

            var sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@Id", SqlDbType.Int) { Value = serviceId };
            using (var sqlHelper = new SqlHelper())
            {
                var dtService = sqlHelper.ExecuteSelectQuery(query, sqlParameters);
                reply = ToServiceList(dtService);
            }
            return reply;
        }

        /// <summary>
        /// Adds the services.
        /// </summary>
        /// <param name="service">The service.</param>
        /// <returns></returns>
        public bool AddServices(Service service)
        {
            const string query =
            @"INSERT INTO Service
            (ContactID,ServiceName,ServiceShortDescription,SubType,ClientDescription,ServiceAttendance,
            ServiceStartExpected,ServiceStartDate,ServiceEndDate,ServiceTimeLimitedMonth,
            ServiceExtendableMonth,ServiceExtendableYear,ServiceTimeLimitedYear,ServiceFullDescription,
            DeptCode,ServiceType,ServiceDescriptionDelivery,ServiceContactCode,
            ServiceContactValue,ContractStagedPayment,ReferralProcessMethod,ContractOutcome,
            Participation,ContractObligation,FundingSource,FundingContactDetails,FundingAmount,
            FundingStart,FundingEnd,FundraisingForText,FundraisingWhy,FundraisingDonorAnonymous,
            FundraisingDonorAmount,FundingNeeds,FundingContinuationAmount,FundingContinuationDetails,
            FundraisingNeeds,FundraisingRequiredBy,FundraisingCompletedDate,FundraisingDonationDate,
            FundraisingDonationIncremental,ServiceBenefitsCriterion,ServiceBarriersCriterion,
            ServiceEthnicityCriterion,ServiceDisabilityCriterion,ServicePersonalCircumtancesCriterion,
            OtherServiceParticipationCriterion,ClientSuportProcess,Intervention,ClientJourney,
            ClientOutcome,TargetClient,Accreditations,ReferalSources,Programme,IsActive)
            VALUES(@ContactID,@ServiceName,@ServiceShortDescription,@SubType,@ClientDescription,
            @ServiceAttendance,@ServiceStartExpected,@ServiceStartDate,@ServiceEndDate,
            @ServiceTimeLimitedMonth,@ServiceExtendableMonth,@ServiceExtendableYear,@ServiceTimeLimitedYear,
            @ServiceFullDescription,@DeptCode,@ServiceType,@ServiceDescriptionDelivery,
            @ServiceContactCode,@ServiceContactValue,@ContractStagedPayment,@ReferralProcessMethod,
            @ContractOutcome,@Participation,@ContractObligation,@FundingSource,@FundingContactDetails,
            @FundingAmount,@FundingStart,@FundingEnd,@FundraisingForText,@FundraisingWhy,
            @FundraisingDonorAnonymous,@FundraisingDonorAmount,@FundingNeeds,@FundingContinuationAmount,
            @FundingContinuationDetails,@FundraisingNeeds,@FundraisingRequiredBy,@FundraisingCompletedDate,
            @FundraisingDonationDate,@FundraisingDonationIncremental,@ServiceBenefitsCriterion,
            @ServiceBarriersCriterion,@ServiceEthnicityCriterion,@ServiceDisabilityCriterion,
            @ServicePersonalCircumtancesCriterion,@OtherServiceParticipationCriterion,@ClientSuportProcess,
            @Intervention,@ClientJourney,@ClientOutcome,@TargetClient,@Accreditations,@ReferalSources,
            @Programme,@IsActive)";
            var parameter = new SqlParameter[56];
            parameter[0] = new SqlParameter
            {
                ParameterName = "@ContactID",
                Value = service.ContactId,
                DbType = DbType.Int32
            };
            parameter[1] = new SqlParameter
            {
                ParameterName = "@ServiceName",
                Value = service.ServiceName,
                SqlDbType = SqlDbType.NVarChar
            };
            parameter[2] = new SqlParameter
            {
                ParameterName = "@ServiceShortDescription",
                Value = service.ServiceShortDescription
            };
            parameter[3] = new SqlParameter
            {
                ParameterName = "@SubType",
                Value = service.SubType,
                SqlDbType = SqlDbType.NVarChar
            };
            parameter[4] = new SqlParameter
            {
                ParameterName = "@ClientDescription",
                Value = service.ClientDescription
            };
            parameter[5] = new SqlParameter
            {
                ParameterName = "@ServiceAttendance",
                Value = service.ServiceAttendance
            };
            parameter[6] = new SqlParameter
            {
                ParameterName = "@ServiceStartExpected",
                Value = service.ServiceStartExpected == null ? DBNull.Value :
                (object)service.ServiceStartExpected,
                SqlDbType = SqlDbType.SmallDateTime
            };
            parameter[7] = new SqlParameter
            {
                ParameterName = "@ServiceStartDate",
                Value = service.ServiceStartDate == null ? DBNull.Value :
                (object)service.ServiceStartDate
            };
            parameter[8] = new SqlParameter
            {
                ParameterName = "@ServiceEndDate",
                Value = service.ServiceEndDate == null ? DBNull.Value :
                (object)service.ServiceEndDate
                
            };
            parameter[9] = new SqlParameter
            {
                ParameterName = "@ServiceTimeLimitedMonth",
                Value = service.ServiceTimeLimitedMonth
            };
            parameter[10] = new SqlParameter
            {
                ParameterName = "@ServiceExtendableMonth",
                Value = service.ServiceExtendableMonth
            };
            parameter[11] = new SqlParameter
            {
                ParameterName = "@ServiceExtendableYear",
                Value = service.ServiceExtendableYear
            };
            parameter[12] = new SqlParameter
            {
                ParameterName = "@ServiceTimeLimitedYear",
                Value = service.ServiceTimeLimitedYear
            };
            parameter[13] = new SqlParameter
            {
                ParameterName = "@ServiceFullDescription",
                Value = service.ServiceFullDescription
            };
            parameter[14] = new SqlParameter { ParameterName = "@DeptCode", Value = service.DeptCode };
            parameter[15] = new SqlParameter
            {
                ParameterName = "@ServiceType",
                Value = service.ServiceType
            };
            parameter[16] = new SqlParameter
            {
                ParameterName = "@ServiceDescriptionDelivery",
                Value = service.ServiceDescriptionDelivery
            };
            parameter[17] = new SqlParameter
            {
                ParameterName = "@ServiceContactCode",
                Value = service.ServiceContactCode
            };
            parameter[18] = new SqlParameter
            {
                ParameterName = "@ServiceContactValue",
                Value = service.ServiceContactValue
            };
            parameter[19] = new SqlParameter
            {
                ParameterName = "@ContractStagedPayment",
                Value = service.ContractStagedPayment
            };
            parameter[20] = new SqlParameter
            {
                ParameterName = "@ReferralProcessMethod",
                Value = service.ReferralProcessMethod
            };
            parameter[21] = new SqlParameter
            {
                ParameterName = "@ContractOutcome",
                Value = service.ContractOutcome
            };
            parameter[22] = new SqlParameter
            {
                ParameterName = "@Participation",
                Value = service.Participation
            };
            parameter[23] = new SqlParameter
            {
                ParameterName = "@ContractObligation",
                Value = service.ContractObligation
            };
            parameter[24] = new SqlParameter
            {
                ParameterName = "@FundingSource",
                Value = service.FundingSource
            };
            parameter[25] = new SqlParameter
            {
                ParameterName = "@FundingContactDetails",
                Value = service.FundingContactDetails
            };
            parameter[26] = new SqlParameter
            {
                ParameterName = "@FundingAmount",
                Value = service.FundingAmount
            };
            parameter[27] = new SqlParameter
            {
                ParameterName = "@FundingStart",
                Value = service.FundingStart == null ? DBNull.Value : (object)service.FundingStart
            };
            parameter[28] = new SqlParameter
            {
                ParameterName = "@FundraisingForText",
                Value = service.FundraisingForText
            };
            parameter[29] = new SqlParameter
            {
                ParameterName = "@FundraisingWhy",
                Value = service.FundraisingWhy
            };
            parameter[30] = new SqlParameter
            {
                ParameterName = "@FundraisingDonorAnonymous",
                Value = service.FundraisingDonorAnonymous
            };
            parameter[31] = new SqlParameter
            {
                ParameterName = "@FundraisingDonorAmount",
                Value = service.FundraisingDonorAmount
            };
            parameter[32] = new SqlParameter
            {
                ParameterName = "@FundingNeeds",
                Value = service.FundingNeeds
            };
            parameter[33] = new SqlParameter
            {
                ParameterName = "@FundingContinuationAmount",
                Value = service.FundingContinuationAmount
            };
            parameter[34] = new SqlParameter
            {
                ParameterName = "@FundingContinuationDetails",
                Value = service.FundingContinuationDetails
            };
            parameter[35] = new SqlParameter
            {
                ParameterName = "@FundraisingNeeds",
                Value = service.FundraisingNeeds
            };
            parameter[36] = new SqlParameter
            {
                ParameterName = "@FundraisingRequiredBy",
                Value = service.FundraisingRequiredBy == null ? DBNull.Value : (object)
                service.FundraisingRequiredBy
            };
            parameter[37] = new SqlParameter
            {
                ParameterName = "@FundraisingCompletedDate",
                Value = service.FundraisingCompletedDate == null ? DBNull.Value : (object)
                service.FundraisingCompletedDate
            };
            parameter[38] = new SqlParameter
            {
                ParameterName = "@FundraisingDonationDate",
                Value = service.FundraisingDonationDate == null ? DBNull.Value : (object)
                service.FundraisingDonationDate
            };
            parameter[39] = new SqlParameter
            {
                ParameterName = "@FundraisingDonationIncremental",
                Value = service.FundraisingDonationIncremental
            };
            parameter[40] = new SqlParameter
            {
                ParameterName = "@ServiceBenefitsCriterion",
                Value = service.ServiceBenefitsCriterion
            };
            parameter[41] = new SqlParameter
            {
                ParameterName = "@ServiceBarriersCriterion",
                Value = service.ServiceBarriersCriterion
            };
            parameter[42] = new SqlParameter
            {
                ParameterName = "@ServiceEthnicityCriterion",
                Value = service.ServiceEthnicityCriterion
            };
            parameter[43] = new SqlParameter
            {
                ParameterName = "@ServiceDisabilityCriterion",
                Value = service.ServiceDisabilityCriterion
            };
            parameter[44] = new SqlParameter
            {
                ParameterName = "@ServicePersonalCircumtancesCriterion",
                Value = service.ServicePersonalCircumtancesCriterion
            };
            parameter[45] = new SqlParameter
            {
                ParameterName = "@OtherServiceParticipationCriterion",
                Value = service.OtherServiceParticipationCriterion
            };
            parameter[46] = new SqlParameter
            {
                ParameterName = "@ClientSuportProcess",
                Value = service.ClientSuportProcess
            };
            parameter[47] = new SqlParameter
            {
                ParameterName = "@Intervention",
                Value = service.Intervention
            };
            parameter[48] = new SqlParameter
            {
                ParameterName = "@ClientJourney",
                Value = service.ClientJourney
            };
            parameter[49] = new SqlParameter
            {
                ParameterName = "@ClientOutcome",
                Value = service.ClientOutcome
            };
            parameter[50] = new SqlParameter
            {
                ParameterName = "@TargetClient",
                Value = service.TargetClient
            };
            parameter[51] = new SqlParameter
            {
                ParameterName = "@Accreditations",
                Value = service.Accreditations
            };
            parameter[52] = new SqlParameter
            {
                ParameterName = "@ReferalSources",
                Value = service.ReferalSources
            };
            parameter[53] = new SqlParameter { ParameterName = "@Programme", Value = service.Programme };
            parameter[54] = new SqlParameter { ParameterName = "@IsActive", Value = service.IsActive };
            parameter[55] = new SqlParameter
            {
                ParameterName = "@FundingEnd",
                Value = service.FundingEnd == null ? DBNull.Value : (object)service.FundingEnd
            };
            using (var sqlHelper = new SqlHelper())
            {
                return sqlHelper.ExecuteInsertQuery(query, parameter);
            }
        }

        /// <summary>
        /// Updates the services.
        /// </summary>
        /// <param name="service">The service.</param>
        /// <returns></returns>
        public bool UpdateServices(Service service)
        {
            const string query = @"UPDATE Service SET ContactID=@ContactID,ServiceName=@ServiceName,
            ServiceShortDescription=@ServiceShortDescription, 
            SubType=@SubType,ClientDescription=@ClientDescription,
            ServiceAttendance=@ServiceAttendance,
            ServiceStartExpected=@ServiceStartExpected,ServiceStartDate=@ServiceStartDate,
            ServiceEndDate=@ServiceEndDate,ServiceTimeLimitedMonth=@ServiceTimeLimitedMonth,
            ServiceExtendableMonth=@ServiceExtendableMonth,ServiceExtendableYear=@ServiceExtendableYear,
            ServiceTimeLimitedYear=@ServiceTimeLimitedYear,ServiceFullDescription=@ServiceFullDescription,
            DeptCode=@DeptCode,ServiceType=@ServiceType,
            ServiceDescriptionDelivery=@ServiceDescriptionDelivery,ServiceContactCode=@ServiceContactCode,
            ServiceContactValue=@ServiceContactValue,ContractStagedPayment=@ContractStagedPayment,
            ReferralProcessMethod=@ReferralProcessMethod,ContractOutcome=@ContractOutcome,
            Participation=@Participation,ContractObligation=@ContractObligation,
            FundingSource=@FundingSource,FundingContactDetails=@FundingContactDetails,
            FundingAmount=@FundingAmount,FundingStart=@FundingStart,
            FundingEnd=@FundingEnd,FundraisingForText=@FundraisingForText,
            FundraisingWhy=@FundraisingWhy,FundraisingDonorAnonymous=@FundraisingDonorAnonymous,
            FundraisingDonorAmount=@FundraisingDonorAmount,FundingNeeds=@FundingNeeds, 
            FundingContinuationAmount=@FundingContinuationAmount,
            FundingContinuationDetails=@FundingContinuationDetails,
            FundraisingNeeds=@FundraisingNeeds,FundraisingRequiredBy=@FundraisingRequiredBy,
            FundraisingCompletedDate=@FundraisingCompletedDate,
            FundraisingDonationDate=@FundraisingDonationDate,
            FundraisingDonationIncremental=@FundraisingDonationIncremental,
            ServiceBenefitsCriterion=@ServiceBenefitsCriterion,
            ServiceBarriersCriterion=@ServiceBarriersCriterion,
            ServiceEthnicityCriterion=@ServiceEthnicityCriterion,
            ServiceDisabilityCriterion=@ServiceDisabilityCriterion,
            ServicePersonalCircumtancesCriterion=@ServicePersonalCircumtancesCriterion,
            OtherServiceParticipationCriterion=@OtherServiceParticipationCriterion,
            ClientSuportProcess=@ClientSuportProcess,Intervention=@Intervention,
            ClientJourney=@ClientJourney,ClientOutcome=@ClientOutcome,TargetClient=@TargetClient,
            Accreditations=@Accreditations,ReferalSources=@ReferalSources,
            Programme=@Programme,IsActive=@IsActive
            WHERE ServiceID=@ServiceID";
            var parameter = new SqlParameter[57];
            parameter[0] = new SqlParameter
            {
                ParameterName = "@ContactID",
                Value = service.ContactId,
                DbType = DbType.Int32
            };
            parameter[1] = new SqlParameter
            {
                ParameterName = "@ServiceName",
                Value = service.ServiceName,
                SqlDbType = SqlDbType.NVarChar
            };
            parameter[2] = new SqlParameter
            {
                ParameterName = "@ServiceShortDescription",
                Value = service.ServiceShortDescription
            };
            parameter[3] = new SqlParameter
            {
                ParameterName = "@SubType",
                Value = service.SubType,
                SqlDbType = SqlDbType.NVarChar
            };
            parameter[4] = new SqlParameter
            {
                ParameterName = "@ClientDescription",
                Value = service.ClientDescription
            };
            parameter[5] = new SqlParameter
            {
                ParameterName = "@ServiceAttendance",
                Value = service.ServiceAttendance
            };
            parameter[6] = new SqlParameter
            {
                ParameterName = "@ServiceStartExpected",
                Value = service.ServiceStartExpected,
                SqlDbType = SqlDbType.SmallDateTime
            };
            parameter[7] = new SqlParameter
            {
                ParameterName = "@ServiceStartDate",
                Value = service.ServiceStartDate,
                SqlDbType = SqlDbType.SmallDateTime
            };
            parameter[8] = new SqlParameter
            {
                ParameterName = "@ServiceEndDate",
                Value = service.ServiceEndDate
            };
            parameter[9] = new SqlParameter
            {
                ParameterName = "@ServiceTimeLimitedMonth",
                Value = service.ServiceTimeLimitedMonth
            };
            parameter[10] = new SqlParameter
            {
                ParameterName = "@ServiceExtendableMonth",
                Value = service.ServiceExtendableMonth
            };
            parameter[11] = new SqlParameter
            {
                ParameterName = "@ServiceExtendableYear",
                Value = service.ServiceExtendableYear
            };
            parameter[12] = new SqlParameter
            {
                ParameterName = "@ServiceTimeLimitedYear",
                Value = service.ServiceTimeLimitedYear
            };
            parameter[13] = new SqlParameter
            {
                ParameterName = "@ServiceFullDescription",
                Value = service.ServiceFullDescription
            };
            parameter[14] = new SqlParameter
            {
                ParameterName = "@DeptCode",
                Value = service.DeptCode
            };
            parameter[15] = new SqlParameter
            {
                ParameterName = "@ServiceType",
                Value = service.ServiceType
            };
            parameter[16] = new SqlParameter
            {
                ParameterName = "@ServiceDescriptionDelivery",
                Value = service.ServiceDescriptionDelivery
            };
            parameter[17] = new SqlParameter
            {
                ParameterName = "@ServiceContactCode",
                Value = service.ServiceContactCode
            };
            parameter[18] = new SqlParameter
            {
                ParameterName = "@ServiceContactValue",
                Value = service.ServiceContactValue
            };
            parameter[19] = new SqlParameter
            {
                ParameterName = "@ContractStagedPayment",
                Value = service.ContractStagedPayment
            };
            parameter[20] = new SqlParameter
            {
                ParameterName = "@ReferralProcessMethod",
                Value = service.ReferralProcessMethod
            };
            parameter[21] = new SqlParameter
            {
                ParameterName = "@ContractOutcome",
                Value = service.ContractOutcome
            };
            parameter[22] = new SqlParameter
            {
                ParameterName = "@Participation",
                Value = service.Participation
            };
            parameter[23] = new SqlParameter
            {
                ParameterName = "@ContractObligation",
                Value = service.ContractObligation
            };
            parameter[24] = new SqlParameter
            {
                ParameterName = "@FundingSource",
                Value = service.FundingSource
            };
            parameter[25] = new SqlParameter
            {
                ParameterName = "@FundingContactDetails",
                Value = service.FundingContactDetails
            };
            parameter[26] = new SqlParameter
            {
                ParameterName = "@FundingAmount",
                Value = service.FundingAmount
            };
            parameter[27] = new SqlParameter
            {
                ParameterName = "@FundingStart",
                Value = service.FundingStart == null ? DBNull.Value : (object)service.FundingStart
            };
            parameter[28] = new SqlParameter
            {
                ParameterName = "@FundraisingForText",
                Value = service.FundraisingForText
            };
            parameter[29] = new SqlParameter
            {
                ParameterName = "@FundraisingWhy",
                Value = service.FundraisingWhy
            };
            parameter[30] = new SqlParameter
            {
                ParameterName = "@FundraisingDonorAnonymous",
                Value = service.FundraisingDonorAnonymous
            };
            parameter[31] = new SqlParameter
            {
                ParameterName = "@FundraisingDonorAmount",
                Value = service.FundraisingDonorAmount
            };
            parameter[32] = new SqlParameter
            {
                ParameterName = "@FundingNeeds",
                Value = service.FundingNeeds
            };
            parameter[33] = new SqlParameter
            {
                ParameterName = "@FundingContinuationAmount",
                Value = service.FundingContinuationAmount
            };
            parameter[34] = new SqlParameter
            {
                ParameterName = "@FundingContinuationDetails",
                Value = service.FundingContinuationDetails
            };
            parameter[35] = new SqlParameter
            {
                ParameterName = "@FundraisingNeeds",
                Value = service.FundraisingNeeds
            };
            parameter[36] = new SqlParameter
            {
                ParameterName = "@FundraisingRequiredBy",
                Value = service.FundraisingRequiredBy == null ? DBNull.Value : (object)service.FundingStart
            };
            parameter[37] = new SqlParameter
            {
                ParameterName = "@FundraisingCompletedDate",
                Value = service.FundraisingCompletedDate == null ? DBNull.Value : (object)service.FundingStart
            };
            parameter[38] = new SqlParameter
            {
                ParameterName = "@FundraisingDonationDate",
                Value = service.FundraisingDonationDate == null ? DBNull.Value : (object)service.FundingStart
            };
            parameter[39] = new SqlParameter
            {
                ParameterName = "@FundraisingDonationIncremental",
                Value = service.FundraisingDonationIncremental
            };
            parameter[40] = new SqlParameter
            {
                ParameterName = "@ServiceBenefitsCriterion",
                Value = service.ServiceBenefitsCriterion
            };
            parameter[41] = new SqlParameter
            {
                ParameterName = "@ServiceBarriersCriterion",
                Value = service.ServiceBarriersCriterion
            };
            parameter[42] = new SqlParameter
            {
                ParameterName = "@ServiceEthnicityCriterion",
                Value = service.ServiceEthnicityCriterion
            };
            parameter[43] = new SqlParameter
            {
                ParameterName = "@ServiceDisabilityCriterion",
                Value = service.ServiceDisabilityCriterion
            };
            parameter[44] = new SqlParameter
            {
                ParameterName = "@ServicePersonalCircumtancesCriterion",
                Value = service.ServicePersonalCircumtancesCriterion
            };
            parameter[45] = new SqlParameter
            {
                ParameterName = "@OtherServiceParticipationCriterion",
                Value = service.OtherServiceParticipationCriterion
            };
            parameter[46] = new SqlParameter
            {
                ParameterName = "@ClientSuportProcess",
                Value = service.ClientSuportProcess
            };
            parameter[47] = new SqlParameter
            {
                ParameterName = "@Intervention",
                Value = service.Intervention
            };
            parameter[48] = new SqlParameter
            {
                ParameterName = "@ClientJourney",
                Value = service.ClientJourney
            };
            parameter[49] = new SqlParameter
            {
                ParameterName = "@ClientOutcome",
                Value = service.ClientOutcome
            };
            parameter[50] = new SqlParameter
            {
                ParameterName = "@TargetClient",
                Value = service.TargetClient
            };
            parameter[51] = new SqlParameter
            {
                ParameterName = "@Accreditations",
                Value = service.Accreditations
            };
            parameter[52] = new SqlParameter
            {
                ParameterName = "@ReferalSources",
                Value = service.ReferalSources
            };
            parameter[53] = new SqlParameter
            {
                ParameterName = "@Programme",
                Value = service.Programme
            };
            parameter[54] = new SqlParameter { ParameterName = "@IsActive", Value = service.IsActive };
            parameter[55] = new SqlParameter
            {
                ParameterName = "@FundingEnd",
                Value = service.FundingEnd == null ? DBNull.Value : (object)service.FundingStart
            };
            parameter[56] = new SqlParameter { ParameterName = "@ServiceID", Value = service.ServiceId };

            using (var sqlHelper = new SqlHelper())
            {
                return sqlHelper.ExecuteUpdateQuery(query, parameter);
            }
        }

        /// <summary>
        /// Starts the with by names.
        /// </summary>
        /// <param name="startWith">The start with.</param>
        /// <returns></returns>
        public DataTable StartWithByNames(string startWith, int flag)
        {
            string query =
            @"Select ServiceID,ServiceName,ServiceShortDescription,ServiceType,Contact.FirstName +' '+
            Contact.Surname as ContactName, Service.IsActive from Service inner join Contact on 
            Service.ContactID = Contact.ContactID";
            if (flag == 1)
            {
                query = startWith.Equals("all") ? query + " order by Service.ServiceName asc" : query +
                    " WHERE  (Service.ServiceName LIKE '['+@ServiceName+']%')" +
                    " order by Service.ServiceName asc";
            }
            else
            {
                query = startWith.Equals("all") ? query +
                    " WHERE Service.IsActive = 1 order by Service.ServiceName asc" : query +
                    " WHERE  (Service.ServiceName LIKE '['+@ServiceName+']%') And Service.IsActive = 1" +
                    " order by Service.ServiceName asc";
            }
            var sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@ServiceName", SqlDbType.NVarChar) { Value = startWith };
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query, sqlParameters);
            }
        }

        /// <summary>
        /// Sets the active service.
        /// </summary>
        /// <param name="serviceId">The service id.</param>
        /// <param name="status">if set to <c>true</c> [status].</param>
        /// <returns></returns>
        public bool SetActiveService(int serviceId, bool status)
        {
            bool reply;
            const string query = @"update Service set IsActive=@IsActive where ServiceID=@ServiceID ";
            using (var helper = new SqlHelper())
            {
                var sqlParameters = new SqlParameter[2];
                sqlParameters[0] = new SqlParameter("@ServiceID", SqlDbType.Int) { Value = serviceId };
                sqlParameters[1] = new SqlParameter("@IsActive", SqlDbType.Bit) { Value = status };
                reply = helper.ExecuteUpdateQuery(query, sqlParameters);
            }
            return reply;
        }

        public DataTable GetByInActive()
        {
            const string query =
            @"Select ServiceID,ServiceName,ServiceShortDescription,ServiceType,Contact.FirstName +' '+
            Contact.Surname as ContactName, Service.IsActive from Service inner join Contact on 
            Service.ContactID = Contact.ContactID  order by Service.ServiceName asc";
            using (var sqlHelper = new SqlHelper())
            {
                return sqlHelper.ExecuteSelectQuery(query);
            }
        }
    }
}